<?php 
	include_once 'dataservice.php';
	include_once 'thuvienham.php';

	class hocvien_db 
	{
		public function Them($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa)
		{
			$sql = "INSERT INTO `tbl_hocvien`(`MaHocVien`, `MaLopHoc`, `MaChuongTrinh`, `MaThoiGianHoc`, `HoTen`, `NgaySinh`, `GioiTinh`,`DiaChi`, `DienThoai`, `Email`, `Lop`, `SoMay`, `NgayDangKy`, `MaTrangThai`, `PIN`, `HocPhiDuocGiam`, `HocPhiDaNop`, `DaXoa`)
					VALUES ('$MaHocVien', " . ($MaLopHoc != '' ? "'$MaLopHoc', " : "NULL,") . " '$MaChuongTrinh', '$MaThoiGianHoc', '$HoTen', '$NgaySinh', '$GioiTinh', '$DiaChi', '$DienThoai', '$Email', '$Lop', '$SoMay', '$NgayDangKy', '$MaTrangThai', '$PIN', '$HocPhiDuocGiam', '$HocPhiDaNop', '$DaXoa')";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}

		public function Xoa($MaHocVien)
		{
			$sql = "DELETE FROM `tbl_hocvien` WHERE `MaHocVien` = '$MaHocVien'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}

		public function CapNhat($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa)
		{
			$sql = "UPDATE `tbl_hocvien` SET ";
			$tmp = 0;
			if($MaLopHoc != "")
			{
				$tmp = 1;
				$sql .= " `MaLopHoc` = " . ($MaLopHoc == '_' ? " NULL " : " '$MaLopHoc' ");
			}
			if($MaChuongTrinh != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `MaChuongTrinh` = '$MaChuongTrinh' ";
			}

			if($MaThoiGianHoc != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `MaThoiGianHoc` = '$MaThoiGianHoc' ";
			}
			if($HoTen != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `HoTen` = '$HoTen' ";
			}

			if($NgaySinh != "0000-00-00")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `NgaySinh` = '$NgaySinh' ";
			}
				
			if($GioiTinh != -1)
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `GioiTinh` = '$GioiTinh' ";
			}
			
			if($DiaChi != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `DiaChi` = '$DiaChi' ";
			}

			if($DienThoai != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `DienThoai` = '$DienThoai' ";
			}

			if($Email != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `Email` = '$Email' ";
			}

			if($Lop != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `Lop` = '$Lop' ";
			}

			if($SoMay != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `SoMay` = '$SoMay' ";
			}

			if($NgayDangKy != "0000-00-00")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `NgayDangKy` = '$NgayDangKy' ";
			}
			
			if($MaTrangThai != -1)
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `MaTrangThai` = '$MaTrangThai' ";
			}

			if($PIN != "")
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `PIN` = '$PIN' ";
			}

			if($HocPhiDuocGiam != -1)
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";
			}

			if($HocPhiDaNop != -1)
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `HocPhiDaNop` = '$HocPhiDaNop' ";
			}

			if($DaXoa != -1)
			{
				$t = ($tmp == 1) ? " , " : "" ;
				$tmp = 1;
				$sql .= $t . "  `DaXoa` = '$DaXoa' ";
			}
			$sql .= " WHERE `MaHocVien` = '$MaHocVien' ";
			//echo $sql . '<br />';
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}

		public function LayDanhSach($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa)
		{
			$sql = "SELECT * FROM `tbl_hocvien` WHERE 1  ";
			if($MaHocVien != "")
				$sql .= " AND `MaHocVien` = '$MaHocVien' ";

			if($MaLopHoc != "")
			{
				if($MaLopHoc == '_')
					$sql .= " AND `MaLopHoc` IS NULL ";
				else
					$sql .= " AND `MaLopHoc` = '$MaLopHoc' ";
			}
			if($MaChuongTrinh != "")
				$sql .= " AND `MaChuongTrinh` = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND `MaThoiGianHoc` = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND `HoTen` LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND `NgaySinh` = '$NgaySinh' ";
				
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND `DiaChi` = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND `DienThoai` = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND `Email` = '$Email' ";

			if($Lop != "")
				$sql .= " AND `Lop` = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND `SoMay` = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND `NgayDangKy` = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND `HocPhiDaNop` = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND `DaXoa` = '$DaXoa' ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function LayDanhSachPaging($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa, $Start, $Limit)
		{
			$sql = "SELECT * FROM `tbl_hocvien` WHERE 1  ";
			if($MaHocVien != "")
				$sql .= " AND `MaHocVien` = '$MaHocVien' ";

			if($MaLopHoc != "")
			{
				if($MaLopHoc == '_')
					$sql .= " AND `MaLopHoc` IS NULL ";
				else
					$sql .= " AND `MaLopHoc` = '$MaLopHoc' ";
			}
			if($MaChuongTrinh != "")
				$sql .= " AND `MaChuongTrinh` = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND `MaThoiGianHoc` = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND `HoTen` LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND `NgaySinh` = '$NgaySinh' ";
			
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND `DiaChi` = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND `DienThoai` = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND `Email` = '$Email' ";

			if($Lop != "")
				$sql .= " AND `Lop` = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND `SoMay` = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND `NgayDangKy` = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND `HocPhiDaNop` = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND `DaXoa` = '$DaXoa' ";
				
			$sql .= " LIMIT $Start, $Limit";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		//List Sort Descending
		public function LSDMaHocVienTheoKhoa($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa, $MaKhoaHoc)
		{
			$sql = "SELECT 	HV.* 
					FROM 	`tbl_hocvien` HV, `tbl_lophoc` LH
					WHERE 	HV.MaLopHoc = LH.MaLopHoc AND
							LH.MaKhoaHoc = '$MaKhoaHoc' ";
			if($MaHocVien != "")
				$sql .= " AND HV.MaHocVien = '$MaHocVien' ";

			if($MaLopHoc != "")
				$sql .= " AND HV.MaLopHoc = '$MaLopHoc' ";

			if($MaChuongTrinh != "")
				$sql .= " AND HV.MaChuongTrinh = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND HV.MaThoiGianHoc = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND HV.HoTen LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND HV.NgaySinh = '$NgaySinh' ";
			
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND HV.DiaChi = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND HV.DienThoai = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND HV.Email = '$Email' ";

			if($Lop != "")
				$sql .= " AND HV.Lop = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND HV.SoMay = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND HV.NgayDangKy = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND HV.MaTrangThai = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND HV.PIN = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND HV.HocPhiDuocGiam = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND HV.HocPhiDaNop = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND HV.DaXoa = '$DaXoa' ";
				
			$sql .= "	ORDER BY HV.MaHocVien DESC ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		//Update PIN
		public function CapNhatPIN($MaHocVien, $PIN)
		{
			$sql = "UPDATE `tbl_hocvien`
					SET `PIN` = '$PIN'
					WHERE `MaHocVien` = '$MaHocVien'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		//Danh sách học viên cần thu học phí
		public function DanhSachHocVienCanThuHocPhi($HoTen)
		{
			$sql = "SELECT 	HV.*, CTH.HocPhi 
					FROM 	`tbl_hocvien` HV, `tbl_chuongtrinhhoc` CTH
					WHERE 	HV.HocPhiDaNop < CTH.HocPhi * (1 - HV.HocPhiDuocGiam / 100) 
							AND HV.DaXoa = 0 ";
			if($HoTen != "")
				$sql .= " AND HV.HoTen LIKE '%$HoTen%' ";
			$sql .= "GROUP BY HV.MaHocVien 
					ORDER BY HV.MaHocVien DESC ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function DanhSachHocVienCanThuHocPhiPaging($HoTen, $Start, $Limit)
		{
			$sql = "SELECT 	HV.*, CTH.HocPhi 
					FROM 	`tbl_hocvien` HV, `tbl_chuongtrinhhoc` CTH
					WHERE 	HV.HocPhiDaNop < CTH.HocPhi * (1 - HV.HocPhiDuocGiam / 100) 
							AND HV.DaXoa = 0 ";
			if($HoTen != "")
				$sql .= " AND HV.HoTen LIKE '%$HoTen%' ";
			$sql .= " GROUP BY HV.MaHocVien 
					ORDER BY HV.MaHocVien DESC ";
			$sql .= " LIMIT $Start, $Limit";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		//Xóa tạm 1 học viên "Move To Trash"
		public function Trash($MaHocVien, $DaXoa)
		{
			$sql = "UPDATE `tbl_hocvien`
					SET `DaXoa` = '$DaXoa'
					WHERE `MaHocVien` = '$MaHocVien'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		public function NopHocPhi($MaHocVien, $HocPhiDaNop)
		{
			$sql = "UPDATE `tbl_hocvien`
					SET `HocPhiDaNop` = $HocPhiDaNop + HocPhiDaNop
					WHERE `MaHocVien` = '$MaHocVien'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function DSHVTheoKhoa($MaKhoa, $MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa) //Hàm này dùng để phát sinh MaHocVien tự động
		{
			$MaKhoa = strtoupper($MaKhoa);
			$tmp = explode("K", $MaKhoa);
			$MK = $tmp[1];
			$sql = "SELECT * FROM `tbl_hocvien` WHERE `MaHocVien` LIKE '$MK.%' " ; 
			
			if($MaHocVien != "")
				$sql .= " AND `MaHocVien` = '$MaHocVien' ";

			if($MaLopHoc != "")
			{
				if($MaLopHoc == '_')
					$sql .= " AND `MaLopHoc` IS NULL ";
				else
					$sql .= " AND `MaLopHoc` = '$MaLopHoc' ";
			}
			if($MaChuongTrinh != "")
				$sql .= " AND `MaChuongTrinh` = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND `MaThoiGianHoc` = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND `HoTen` LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND `NgaySinh` = '$NgaySinh' ";
			
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND `DiaChi` = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND `DienThoai` = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND `Email` = '$Email' ";

			if($Lop != "")
				$sql .= " AND `Lop` = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND `SoMay` = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND `NgayDangKy` = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND `HocPhiDaNop` = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND `DaXoa` = '$DaXoa' ";
			
			$sql .= " ORDER BY `MaHocVien` DESC ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function DSHVTheoKhoaPaging($MaKhoa, $MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa, $Start, $Limit) //Hàm này dùng để phát sinh MaHocVien tự động
		{
			$tmp = explode("K", $MaKhoa);
			$MK = $tmp[1];
			$sql = "SELECT * FROM `tbl_hocvien` WHERE `MaHocVien` LIKE '$MK.%' " ; 
			
			if($MaHocVien != "")
				$sql .= " AND `MaHocVien` = '$MaHocVien' ";

			if($MaLopHoc != "")
			{
				if($MaLopHoc == '_')
					$sql .= " AND `MaLopHoc` IS NULL ";
				else
					$sql .= " AND `MaLopHoc` = '$MaLopHoc' ";
			}
			if($MaChuongTrinh != "")
				$sql .= " AND `MaChuongTrinh` = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND `MaThoiGianHoc` = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND `HoTen` LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND `NgaySinh` = '$NgaySinh' ";
			
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND `DiaChi` = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND `DienThoai` = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND `Email` = '$Email' ";

			if($Lop != "")
				$sql .= " AND `Lop` = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND `SoMay` = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND `NgayDangKy` = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND `HocPhiDaNop` = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND `DaXoa` = '$DaXoa' ";
			$sql .= " ORDER BY `MaHocVien` DESC LIMIT $Start, $Limit ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function LayDanhSachSapXepTheoTen($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa)
		{
			$sql = "SELECT * FROM `tbl_hocvien` WHERE 1  ";
			if($MaHocVien != "")
				$sql .= " AND `MaHocVien` = '$MaHocVien' ";

			if($MaLopHoc != "")
				$sql .= " AND `MaLopHoc` = '$MaLopHoc' ";

			if($MaChuongTrinh != "")
				$sql .= " AND `MaChuongTrinh` = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND `MaThoiGianHoc` = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND `HoTen` LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND `NgaySinh` = '$NgaySinh' ";
				
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND `DiaChi` = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND `DienThoai` = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND `Email` = '$Email' ";

			if($Lop != "")
				$sql .= " AND `Lop` = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND `SoMay` = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND `NgayDangKy` = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND `HocPhiDaNop` = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND `DaXoa` = '$DaXoa' ";
			$sql .= " ORDER BY SUBSTRING_INDEX( TRIM( HoTen ) ,  ' ', -1 ) ASC ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function LayDanhSachSapXepTheoSoMay($MaHocVien, $MaLopHoc, $MaChuongTrinh, $MaThoiGianHoc, $HoTen, $NgaySinh, $GioiTinh, $DiaChi, $DienThoai, $Email, $Lop, $SoMay, $NgayDangKy, $MaTrangThai, $PIN, $HocPhiDuocGiam, $HocPhiDaNop, $DaXoa)
		{
			$sql = "SELECT * FROM `tbl_hocvien` WHERE 1  ";
			if($MaHocVien != "")
				$sql .= " AND `MaHocVien` = '$MaHocVien' ";

			if($MaLopHoc != "")
				$sql .= " AND `MaLopHoc` = '$MaLopHoc' ";

			if($MaChuongTrinh != "")
				$sql .= " AND `MaChuongTrinh` = '$MaChuongTrinh' ";

			if($MaThoiGianHoc != "")
				$sql .= " AND `MaThoiGianHoc` = '$MaThoiGianHoc' ";

			if($HoTen != "")
				$sql .= " AND `HoTen` LIKE '%$HoTen%' ";

			if($NgaySinh != "0000-00-00")
				$sql .= " AND `NgaySinh` = '$NgaySinh' ";
				
			if($GioiTinh != -1)
				$sql .= " AND `GioiTinh` = '$GioiTinh' ";
			
			if($DiaChi != "")
				$sql .= " AND `DiaChi` = '$DiaChi' ";

			if($DienThoai != "")
				$sql .= " AND `DienThoai` = '$DienThoai' ";

			if($Email != "")
				$sql .= " AND `Email` = '$Email' ";

			if($Lop != "")
				$sql .= " AND `Lop` = '$Lop' ";

			if($SoMay != "")
				$sql .= " AND `SoMay` = '$SoMay' ";

			if($NgayDangKy != "0000-00-00")
				$sql .= " AND `NgayDangKy` = '$NgayDangKy' ";

			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";

			if($HocPhiDuocGiam != -1)
				$sql .= " AND `HocPhiDuocGiam` = '$HocPhiDuocGiam' ";

			if($HocPhiDaNop != -1)
				$sql .= " AND `HocPhiDaNop` = '$HocPhiDaNop' ";

			if($DaXoa != -1)
				$sql .= " AND `DaXoa` = '$DaXoa' ";
			$sql .= " ORDER BY SoMay ASC ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
	}

